import traceback

import pymysql

# 1.定义一个方法
from config import phone_data, phone_data2


def exe_sql(sql_str):
    conn = None
    cursor = None

    try:
        # 2.创建数据库连接对象
        conn = pymysql.connect(host="121.43.169.97", user="root", password="Itcast_p2p_20191228",
                               database="czbk_member",
                               charset='utf8')

        # 3.创建游标对象
        cursor = conn.cursor()

        # 4.在游标对象中执行SQL语句
        cursor.execute(sql_str)

        # 如果是查询语句
        # 返回全部查询结果
        if sql_str.split(" ")[0].lower() == "select":
            return cursor.fetchall()
        # 如果是非查询语句
        # 手动提交数据事务
        # 返回受影响的行数
        else:
            conn.commit()
            return f"受影响的行数为：{cursor.rowcount}"
    except:
        # 有异常的情况下，进行数据库事务回滚
        if conn is not None:
            conn.rollback()
        traceback.format_exc()
    finally:
        if cursor is not None:
            # 5.关闭游标对象
            cursor.close()
        if conn is not None:
            # 6.关闭数据库连接对象
            conn.close()


# 封装清洗用户信息的方法
def clear_user_info():
    # 需要执行SQL语句
    sql1 = f"delete i.* from mb_member_info i inner join mb_member m on m.id = i.member_id where m.phone in {phone_data};"
    sql2 = f"delete l.* from mb_member_login_log l inner join mb_member m on m.id=l.member_id where m.phone in {phone_data};"
    sql3 = f"delete from mb_member where phone in {phone_data};"
    sql4 = f"delete from mb_member_register_log where phone in {phone_data}；"
    # 调用封装操作数据库的方法，执行SQL语句
    exe_sql(sql1)
    exe_sql(sql2)
    exe_sql(sql3)
    exe_sql(sql4)
    return exe_sql(sql1), exe_sql(sql2), exe_sql(sql3), exe_sql(sql4)

def clear_user_info2():
    # 需要执行SQL语句
    sql1 = f"delete i.* from mb_member_info i inner join mb_member m on m.id = i.member_id where m.phone in {phone_data2};"
    sql2 = f"delete l.* from mb_member_login_log l inner join mb_member m on m.id=l.member_id where m.phone in {phone_data2};"
    sql3 = f"delete from mb_member where phone in {phone_data2};"
    sql4 = f"delete from mb_member_register_log where phone in {phone_data2}；"
    # 调用封装操作数据库的方法，执行SQL语句
    exe_sql(sql1)
    exe_sql(sql2)
    exe_sql(sql3)
    exe_sql(sql4)
    return exe_sql(sql1), exe_sql(sql2), exe_sql(sql3), exe_sql(sql4)


if __name__ == '__main__':
    a = clear_user_info()
    print(a)
